package gov.pbc.nn.kjgl.meetings.dao;

import gov.pbc.nn.core.bdf.entity.Dept;
import gov.pbc.nn.core.dao.AbstractJdbcDao;
import gov.pbc.nn.core.dao.CommonHibernateDao;
import gov.pbc.nn.core.dao.PbcnnCoreJdbcDao;
import gov.pbc.nn.core.utils.SecurityUtils;
import gov.pbc.nn.kjgl.Constants;
import gov.pbc.nn.kjgl.entity.Meeting;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.bstek.bdf2.core.context.ContextHolder;
import com.bstek.bdf2.core.orm.ParseResult;
import com.bstek.dorado.data.entity.EntityUtils;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Page;

@Repository
public class MeetingJdbcDao extends AbstractJdbcDao {

	@Resource(name = PbcnnCoreJdbcDao.BEAN_ID)
	private PbcnnCoreJdbcDao pbcnnCoreJdbcDao;

	@Resource(name = CommonHibernateDao.BEAN_ID)
	private CommonHibernateDao hibernateDao;

	@Autowired
	private MeetingDao meetingDao;

	public void getMeetingByRelease(Page<Meeting> page, Criteria criteria) {
		String sql = "select a.*,B.END_TIME_ from t_meeting as a inner join t_meeting_dept as b on a.ID_=b.MEETING_ID_ where b.DEPT_ID_ like :deptId and a.STATE_=15 and a.INVALID_=0 ";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("deptId", ((Dept) ContextHolder.getLoginUser().getDepts().get(0)).getCDept().getId() + "%");
		ParseResult pr = parseCriteria(criteria, true, "a");
		if (pr != null) {
			StringBuffer sbSql = pr.getAssemblySql();
			sql += " and " + sbSql + " order by a.CREATE_DATE_ desc";
			map.putAll(pr.getValueMap());
		} else {
			sql += "order by a.CREATE_DATE_ desc";
		}
		this.pagingQuery(page, sql, new RowMapper<Meeting>() {
			@Override
			public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return MeetingJdbcDao.this.mapRow(rs, rowNum, 1);
			}
		}, map);
	}

	/**
	 * 根据当前登录人 获取Meeting
	 * 
	 * @param page
	 */
	public void getMeeting(Page<Meeting> page, Criteria criteria) {
		String userName = ContextHolder.getLoginUser().getUsername();
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select * from t_meeting where CREATE_USER_=:username and INVALID_=0 ";
		param.put("username", userName);
		ParseResult pr = parseCriteria(criteria, true, null);
		if (pr != null) {
			StringBuffer sbSql = pr.getAssemblySql();
			sql += " and " + sbSql.toString()
					+ " order by case when STATE_=11 then 100 when STATE_=10 then 99 end desc,CREATE_DATE_ desc ";
			param.putAll(pr.getValueMap());
		}
		this.pagingQuery(page, sql, new RowMapper<Meeting>() {

			@Override
			public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
			}

		}, param);
	}

	/**
	 * 根据不同条件查询Meeting
	 * 
	 * @param page
	 * @param i
	 */
	public void getMeetingAll(Page<Meeting> page, String i, Criteria criteria) {
		// i=sbYes/No时表示 我申报的已完成/未完成
		// i=cbYes/No时表示 我参报的已完成/未完成
		// i=jbYes/No时表示 我经办的已完成/未完成
		// i=cyYes/No时表示 我参与的已完成/未完成
		// i=allYes/No时表示 全部 已完成/未完成

		String sql = "select a.* from t_meeting as a ";
		String loginUser = ContextHolder.getLoginUserName();
		Map<String, Object> map = new HashMap<String, Object>();
		ParseResult pr = parseCriteria(criteria, true, "a");
		if (i.equals("sbYes")) {
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += "where a.CREATE_USER_=:username and a.INVALID_=0 and a.STATE_=100 and " + sbSql
						+ " order by a.CREATE_DATE_ desc";
				map.putAll(pr.getValueMap());
			} else {
				sql += "where a.CREATE_USER_=:username and a.INVALID_=0 and a.STATE_=100 order by a.CREATE_DATE_ desc";
			}
			map.put("username", loginUser);
			this.pagingQuery(page, sql, new RowMapper<Meeting>() {
				@Override
				public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
				}
			}, map);
		} else if (i.equals("sbNo")) {
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += "where a.CREATE_USER_=:username and a.INVALID_=0 and a.STATE_!=100 and " + sbSql
						+ " order by a.CREATE_DATE_ desc";
				map.putAll(pr.getValueMap());
			} else {
				sql += "where a.CREATE_USER_=:username and a.INVALID_=0 and a.STATE_!=100 order by a.CREATE_DATE_ desc";
			}
			map.put("username", loginUser);
			this.pagingQuery(page, sql, new RowMapper<Meeting>() {
				@Override
				public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
				}
			}, map);
		} else if (i.equals("cbYes")) {
			ParseResult prs = parseCriteria(criteria, true, null);
			if (prs != null) {
				StringBuffer sbSql = prs.getAssemblySql();
				sql = "SELECT * from t_meeting m where m.ID_ in ( select DISTINCT u.MEETING_ID_ from t_meeting_user u where u.CREATE_USER_=:username ) and m.INVALID_=0 and m.STATE_=100 and "
						+ sbSql + " order by CREATE_DATE_ desc";
				map.putAll(prs.getValueMap());
			} else {
				sql = "SELECT * from t_meeting m where m.ID_ in ( select DISTINCT u.MEETING_ID_ from t_meeting_user u where u.CREATE_USER_=:username ) and m.INVALID_=0 and m.STATE_=100 order by CREATE_DATE_ desc";
			}
			map.put("username", loginUser);
			this.pagingQuery(page, sql, new RowMapper<Meeting>() {
				@Override
				public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
				}
			}, map);

		} else if (i.equals("cbNo")) {
			ParseResult prs = parseCriteria(criteria, true, null);
			if (prs != null) {
				StringBuffer sbSql = prs.getAssemblySql();
				sql = "SELECT * from t_meeting m where m.ID_ in ( select DISTINCT u.MEETING_ID_ from t_meeting_user u where u.CREATE_USER_=:username ) and m.INVALID_=0 and m.STATE_!=100 and "
						+ sbSql + " order by CREATE_DATE_ desc";
				map.putAll(prs.getValueMap());
			} else {
				sql = "SELECT * from t_meeting m where m.ID_ in ( select DISTINCT u.MEETING_ID_ from t_meeting_user u where u.CREATE_USER_=:username ) and m.INVALID_=0 and m.STATE_!=100 order by CREATE_DATE_ desc";
			}
			map.put("username", loginUser);
			this.pagingQuery(page, sql, new RowMapper<Meeting>() {
				@Override
				public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
				}
			}, map);

		} else if (i.equals("jbYes")) {
			sql = "select DISTINCT o.BUS_KEY_ from t_uflo_opinion o where o.USERNAME_=? and o.BUS_TABLE_=?";
			List<String> ids = this.pbcnnCoreJdbcDao.getJdbcTemplate().query(sql,
					new Object[] { ContextHolder.getLoginUserName(), "T_MEETING" }, new RowMapper<String>() {

						@Override
						public String mapRow(ResultSet rs, int rowNum) throws SQLException {
							return rs.getString("BUS_KEY_");
						}

					});
			if (ids.isEmpty()) {
				return;
			}
			DetachedCriteria dc = this.hibernateDao.buildDetachedCriteria(criteria, Meeting.class);
			dc.add(Restrictions.or(Restrictions.eq("invalid", false), Restrictions.isNull("invalid")));
			dc.add(Restrictions.in("id", ids));
			dc.add(Restrictions.eq("state", 100));
			dc.addOrder(Order.desc("createDate"));
			this.hibernateDao.pagingQuery(page, dc);

		} else if (i.equals("jbNo")) {
			sql = "select DISTINCT o.BUS_KEY_ from t_uflo_opinion o where o.USERNAME_=? and o.BUS_TABLE_=?";
			List<String> ids = this.pbcnnCoreJdbcDao.getJdbcTemplate().query(sql,
					new Object[] { ContextHolder.getLoginUserName(), "T_MEETING" }, new RowMapper<String>() {

						@Override
						public String mapRow(ResultSet rs, int rowNum) throws SQLException {
							return rs.getString("BUS_KEY_");
						}

					});
			if (ids.isEmpty()) {
				return;
			}
			DetachedCriteria dc = this.hibernateDao.buildDetachedCriteria(criteria, Meeting.class);
			dc.add(Restrictions.or(Restrictions.eq("invalid", false), Restrictions.isNull("invalid")));
			dc.add(Restrictions.in("id", ids));
			dc.addOrder(Order.desc("createDate"));
			dc.add(Restrictions.ne("state", 100));
			this.hibernateDao.pagingQuery(page, dc);

		} else if (i.equals("cyYes")) {
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += "inner join t_meeting_user as b on a.ID_=b.MEETING_ID_ where b.USER_NAME_=:username and b.INVALID_=0 and b.JOIN_=1 and a.STATE_=100 and "
						+ sbSql + " order by a.CREATE_DATE_ desc";
				map.putAll(pr.getValueMap());
			} else {
				sql += "inner join t_meeting_user as b on a.ID_=b.MEETING_ID_ where b.USER_NAME_=:username and b.INVALID_=0 and b.JOIN_=1 and a.STATE_=100 order by a.CREATE_DATE_ desc";
			}
			map.put("username", loginUser);
			this.pagingQuery(page, sql, new RowMapper<Meeting>() {
				@Override
				public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
				}
			}, map);
		} else if (i.equals("cyNo")) {
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += "inner join t_meeting_user as b on a.ID_=b.MEETING_ID_ where b.USER_NAME_=:username and b.INVALID_=0 and b.JOIN_=1 and a.STATE_!=100 and "
						+ sbSql + " order by a.CREATE_DATE_ desc";
				map.putAll(pr.getValueMap());
			} else {
				sql += "inner join t_meeting_user as b on a.ID_=b.MEETING_ID_ where b.USER_NAME_=:username and b.INVALID_=0 and b.JOIN_=1 and a.STATE_!=100 order by a.CREATE_DATE_ desc";
			}
			map.put("username", loginUser);
			this.pagingQuery(page, sql, new RowMapper<Meeting>() {
				@Override
				public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
				}
			}, map);
		} else if (i.equals("allYes")) {
			if (SecurityUtils.containsRole(ContextHolder.getLoginUserName(), Constants.ROLE_HYGLY)) {
				ParseResult prs = parseCriteria(criteria, true, "a");
				if (prs != null) {
					StringBuffer sbSql = prs.getAssemblySql();
					sql += " where a.INVALID_=0 and a.STATE_=100 and " + sbSql + " order by a.CREATE_DATE_ desc ";
					map.putAll(prs.getValueMap());
				} else {
					sql += " where a.INVALID_=0 and a.STATE_=100 order by a.CREATE_DATE_ desc";
				}
				this.pagingQuery(page, sql, new RowMapper<Meeting>() {
					@Override
					public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
						// TODO Auto-generated method stub
						return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
					}
				}, map);
			}
		} else if (i.equals("allNo")) {
			if (SecurityUtils.containsRole(ContextHolder.getLoginUserName(), Constants.ROLE_HYGLY)) {
				ParseResult prs = parseCriteria(criteria, true, "a");
				if (prs != null) {
					StringBuffer sbSql = prs.getAssemblySql();
					sql += " where a.INVALID_=0 and a.STATE_!=100 and " + sbSql + " order by a.CREATE_DATE_ desc ";
					map.putAll(prs.getValueMap());
				} else {
					sql += " where a.INVALID_=0 and a.STATE_!=100 order by a.CREATE_DATE_ desc";
				}
				this.pagingQuery(page, sql, new RowMapper<Meeting>() {
					@Override
					public Meeting mapRow(ResultSet rs, int rowNum) throws SQLException {
						// TODO Auto-generated method stub
						return MeetingJdbcDao.this.mapRow(rs, rowNum, 0);
					}
				}, map);
			}
		}
	}

	public List<Map<String, Object>> getMeetingXqDelete(String MeetingId) {
		String sql = "select a.ID_,a.USER_CNAME_,b.DEPT_NAME_ from t_meeting_user a inner join t_meeting_dept b on a.MEETING_ID_=b.MEETING_ID_ where b.MEETING_ID_=? and b.DEPT_ID_=? and a.INVALID_=0";
		List<Map<String, Object>> ls = this.getJdbcTemplate().query(sql, new RowMapper<Map<String, Object>>() {
			@Override
			public Map<String, Object> mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("cname", rs.getString("USER_CNAME_"));
				m.put("id", rs.getString("ID_"));
				m.put("deptName", rs.getString("DEPT_NAME_"));
				return m;
			}
		}, new Object[] { MeetingId, ContextHolder.getLoginUser().getDepts().get(0).getId() });
		return ls;
	}

	public Meeting mapRow(ResultSet rs, int rowNum, int i) throws SQLException {
		Meeting m = new Meeting();
		m.setId(rs.getString("ID_"));
		m.setName(rs.getString("NAME_"));
		m.setCreateDate(rs.getTimestamp("CREATE_DATE_"));
		m.setUpdateDate(rs.getTimestamp("UPDATE_DATE_"));
		m.setCreateUser(rs.getString("CREATE_USER_"));
		m.setUpdateUser(rs.getString("UPDATE_USER_"));
		m.setAllNum(rs.getInt("ALL_NUM_"));
		m.setAssessor(rs.getString("ASSESSOR_"));
		m.setAssessorId(rs.getString("ASSESSOR_ID_"));
		m.setCldSh(rs.getInt("CLD_SH_"));
		m.setContent(rs.getString("CONTENT_"));
		m.setDay(rs.getInt("DAY_"));
		m.setDszzNum(rs.getInt("DSZZ_NUM_"));
		m.setForm(rs.getInt("FORM_"));
		m.setJoinUnit(rs.getString("JOIN_UNIT_"));
		m.setMeetNum(rs.getInt("MEET_NUM_"));
		m.setMoney(rs.getDouble("MONEY_"));
		m.setNnpbcNum(rs.getInt("NNPBC_NUM_"));
		m.setNnxzhNum(rs.getInt("NNXZH_NUM_"));
		m.setOther(rs.getInt("OTHER_"));
		m.setOutUnitNum(rs.getInt("OUT_UNIT_NUM_"));
		m.setPlace(rs.getString("PLACE_"));
		m.setPlaceDemand(rs.getString("PLACE_DEMAND_"));
		m.setRemark(rs.getString("REMARK_"));
		m.setSpokesman(rs.getString("SPOKESMAN_"));
		m.setState(rs.getInt("STATE_"));
		m.setTime(rs.getTimestamp("TIME_"));
		m.setType(rs.getInt("TYPE_"));
		m.setApplyUser(rs.getString("APPLY_USER_"));
		m.setApplyDeptId(rs.getString("APPLY_DEPT_ID_"));
		m.setFileName(rs.getString("FILE_NAME_"));
		m.setFileUrl(rs.getString("FILE_URL_"));
		if (i == 1) {
			try {
				m = EntityUtils.toEntity(m);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			EntityUtils.setValue(m, "endTime", rs.getTimestamp("END_TIME_"));
		}
		return m;
	}
}
